﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WebPromenadebyenTabeller;
using System.Data.SqlClient;
using System.Configuration;

namespace PromPPladsDB
{
    public static class ParkeringspladsGateway

    {
        const String CONNECTION_STRING = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PromDatabase.mdf;Integrated Security=True;User Instance=True";
       
        public static List<Parkeringsplads> FindAllePPladser()
        {
            List<Parkeringsplads> nyListe = new List<Parkeringsplads>();

            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = CONNECTION_STRING;
            con.Open();

            //SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            
            System.Data.IDbCommand command = con.CreateCommand();
            command.CommandText = "SELECT * FROM PARKERINGSPLADSER";
            System.Data.IDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                int parkeringsID = int.Parse(reader["parkeringsID"].ToString());
                int lejlighedsID = int.Parse(reader["lejlighedsID"].ToString());
                int koeretoej =    int.Parse(reader["koeretoej"].ToString());

                Parkeringsplads nyPPlads = new Parkeringsplads(parkeringsID, lejlighedsID, koeretoej);

                nyListe.Add(nyPPlads);

            }

            con.Close();
            return nyListe;
        }

        public static Parkeringsplads FindParkeringsplads(int ParkeringsID)
        {
            //SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = CONNECTION_STRING;
            
            con.Open();

            System.Data.IDbCommand command = con.CreateCommand();

            command.CommandText = "SELECT * FROM PARKERINGSPLADSER WHERE (parkeringsID =" + ParkeringsID + " )";
            System.Data.IDataReader reader = command.ExecuteReader();

            reader.Read();

            int parkeringsID = int.Parse(reader["parkeringsID"].ToString());
            int lejlighedsID = int.Parse(reader["lejlighedsID"].ToString());
            int koeretoej = int.Parse(reader["koeretoej"].ToString());

            Parkeringsplads nyParkeringsplads = new Parkeringsplads(parkeringsID, lejlighedsID, koeretoej);

            con.Close();
            return nyParkeringsplads;
        }


        public static Parkeringsplads OpretPPlads(int lejlighedsID, int koeretoej)
        {
            Parkeringsplads nyPPlads = null;

            //SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = CONNECTION_STRING;
            
            con.Open();

            System.Data.IDbCommand command = con.CreateCommand();
     
            command.CommandText = "INSERT INTO PARKERINGSPLADSER "
                                    + "(lejlighedsID, koeretoej) VALUES " +
                                    "(" + lejlighedsID + ", " + koeretoej + ")";
          


            int antalIndsat = command.ExecuteNonQuery();
            if (antalIndsat == 1)
            {
                command.CommandText = "SELECT @@identity AS lastinsertedkey";
                string slastkey = command.ExecuteScalar().ToString();
                int lastkey = int.Parse(slastkey);
                command.CommandText = "SELECT * FROM PARKERINGSPLADSER "
                                       + " WHERE (parkeringsID=" + lastkey + ")";
                System.Data.IDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    int nyparkeringsID = int.Parse(reader["parkeringsID"].ToString());
                    int nylejlighedsID = int.Parse(reader["lejlighedsID"].ToString());
                    int nykoeretoej = int.Parse(reader["koeretoej"].ToString());

                    nyPPlads = new Parkeringsplads(nyparkeringsID, nylejlighedsID, nykoeretoej);
                }
            }
            con.Close();

            return nyPPlads;
        }

        public static void OpdaterParkeringsplads(int parkeringsID, int lejlighedsID, int koeretoej)
        {
            //SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = CONNECTION_STRING;
            
            con.Open();

            System.Data.IDbCommand command = con.CreateCommand();
            command.CommandText = "UPDATE PARKERINGSPLADSER SET " + "lejlighedsID=" + lejlighedsID + " " + ", koeretoej=" + koeretoej + " " + "WHERE parkeringsID=" + parkeringsID;
            int antalOpdateret = command.ExecuteNonQuery();

            con.Close();
        }




    }
}
